const char* COURSE_NAMES[5] = {"高等数学", "线性代数", "高级语言程序设计", "大学物理", "离散数学"};

// 数据库操作函数声明
int init_database();
int add_student_db(const char* name, int id, const char* classroom);
int update_student_db(int id, const char* name, const char* classroom);
int delete_student_db(int id);
int input_scores_db(int id, float* daily_scores, float* final_scores);
int query_student_by_id_db(int id, char* name, char* classroom, 
                          float* daily_scores, float* final_scores, float* total_score, int* rank);
int query_student_by_name_db(const char* name, char* classroom, int* id, 
                            float* daily_scores, float* final_scores, float* total_score, int* rank);
int statistics_class_db(const char* classroom, int* count, float* total, float* max, float* min);
float calculate_pass_score(float known_score, int course_idx, int score_type);
void close_database();

sqlite3* db = NULL;

// 辅助函数：转义SQL字符串中的单引号
char* escape_sql_string(const char* str) {
    if (!str) return NULL;
    
    int len = strlen(str);
    int escaped_len = len;
    for (int i = 0; i < len; i++) {
        if (str[i] == '\'') escaped_len++;
    }
    
    char* escaped = (char*)malloc((escaped_len + 1) * sizeof(char));
    if (!escaped) return NULL;
    
    int j = 0;
    for (int i = 0; i < len; i++) {
        if (str[i] == '\'') {
            escaped[j++] = '\'';
        }
        escaped[j++] = str[i];
    }
    escaped[j] = '\0';
    
    return escaped;
}

// 初始化数据库并创建表
int init_database() {
    int rc = sqlite3_open(DB_FILENAME, &db);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "无法打开数据库: %s\n", sqlite3_errmsg(db));
        return 0;
    }
    
    // 创建学生表
    const char* sql_students = "CREATE TABLE IF NOT EXISTS students ("
                                "id INTEGER PRIMARY KEY,"
                                "name TEXT NOT NULL,"
                                "classroom TEXT"
                                ")";
                            
    // 创建成绩表（包含平时分和期末分）
    const char* sql_scores = "CREATE TABLE IF NOT EXISTS scores ("
                              "id INTEGER PRIMARY KEY,"
                              "student_id INTEGER NOT NULL,"
                              "course1_daily REAL,"
                              "course1_final REAL,"
                              "course2_daily REAL,"
                              "course2_final REAL,"
                              "course3_daily REAL,"
                              "course3_final REAL,"
                              "course4_daily REAL,"
                              "course4_final REAL,"
                              "course5_daily REAL,"
                              "course5_final REAL,"
                              "total_score REAL,"
                              "FOREIGN KEY (student_id) REFERENCES students (id)"
                              ")";
                            
    char* errmsg = NULL;
    rc = sqlite3_exec(db, sql_students, NULL, NULL, &errmsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "创建学生表失败: %s\n", errmsg);
        sqlite3_free(errmsg);
        sqlite3_close(db);
        return 0;
    }
    
    rc = sqlite3_exec(db, sql_scores, NULL, NULL, &errmsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "创建成绩表失败: %s\n", errmsg);
        sqlite3_free(errmsg);
        sqlite3_close(db);
        return 0;
    }
    
    printf("数据库初始化成功\n");
    return 1;
}

// 关闭数据库连接
void close_database() {
    if (db) {
        sqlite3_close(db);
        db = NULL;
    }
}
void input_scores() {
    if (db == NULL && !init_database()) {
        printf("数据库初始化失败，无法录入成绩\n");
        return;
    }

    int id;
    printf("请输入要录入成绩的学生学号：");
    scanf("%d", &id);

    float daily_scores[5] = {0};
    float final_scores[5] = {0};
    
    printf("请依次输入5门课程的平时分和期末分（分数范围0-100）：\n");
    for (int j = 0; j < 5; j++) {
        while (1) {
            printf("%s - 平时分：", COURSE_NAMES[j]);
            scanf("%f", &daily_scores[j]);
            if (daily_scores[j] < 0 || daily_scores[j] > 100) {
                printf("分数必须在0-100之间，请重新输入！\n");
            } else {
                break;
            }
        }
        
        while (1) {
            printf("%s - 期末分：", COURSE_NAMES[j]);
            scanf("%f", &final_scores[j]);
            if (final_scores[j] < 0 || final_scores[j] > 100) {
                printf("分数必须在0-100之间，请重新输入！\n");
            } else {
                break;
            }
        }
    }
    
    if (input_scores_db(id, daily_scores, final_scores)) {
        printf("成绩录入成功！\n");
    } else {
        printf("成绩录入失败！\n");
    }
}

int input_scores_db(int id, float* daily_scores, float* final_scores) {
    char sql[2048];
    char* errmsg = NULL;
    
    float total = 0;
    for (int j = 0; j < 5; j++) {
        // 计算加权成绩：平时分占40%，期末分占60%
        float course_score = daily_scores[j] * 0.4 + final_scores[j] * 0.6;
        total += course_score;
    }
    
    sprintf(sql, "UPDATE scores SET "
                 "course1_daily = %.2f, course1_final = %.2f, "
                 "course2_daily = %.2f, course2_final = %.2f, "
                 "course3_daily = %.2f, course3_final = %.2f, "
                 "course4_daily = %.2f, course4_final = %.2f, "
                 "course5_daily = %.2f, course5_final = %.2f, "
                 "total_score = %.2f "
                 "WHERE student_id = %d", 
                 daily_scores[0], final_scores[0],
                 daily_scores[1], final_scores[1],
                 daily_scores[2], final_scores[2],
                 daily_scores[3], final_scores[3],
                 daily_scores[4], final_scores[4],
                 total, id);
    int rc = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "更新成绩数据失败: %s\n", errmsg);
        sqlite3_free(errmsg);
        return 0;
    }
    
    return 1;
}

void query_scores() {
    if (db == NULL && !init_database()) {
        printf("数据库初始化失败，无法查询成绩\n");
        return;
    }

    int choice;
    printf("请选择查询方式：\n1.按学号查询 \n2.按姓名查询：");
    scanf("%d", &choice);

    if (choice == 1) {
        int id;
        printf("请输入要查询的学生学号：");
        scanf("%d", &id);

        char name[MAX_NAME_LENGTH];
        char classroom[MAX_NAME_LENGTH];
        float daily_scores[5] = {0};
        float final_scores[5] = {0};
        float total = 0;
        int rank = 0;
        
        if (query_student_by_id_db(id, name, classroom, daily_scores, final_scores, &total, &rank)) {
            printf("学生姓名：%s\n", name);
            printf("学号：%d\n", id);
            printf("班级：%s\n", classroom);
            printf("成绩详情：\n");
            for (int j = 0; j < 5; j++) {
                float course_score = daily_scores[j] * 0.4 + final_scores[j] * 0.6;
                printf("%s - 平时分：%.2f，期末分：%.2f，加权分：%.2f\n", 
                       COURSE_NAMES[j], daily_scores[j], final_scores[j], course_score);
            }
            printf("总成绩：%.2f\n", total);
            printf("在所有学生中排名：第%d名\n", rank);
        } else {
            printf("未找到该学生！\n");
        }
    } else if (choice == 2) {
        char name[MAX_NAME_LENGTH];
        printf("请输入要查询的学生姓名：");
        scanf("%s", name);

        char classroom[MAX_NAME_LENGTH];
        int id = 0;
        float daily_scores[5] = {0};
        float final_scores[5] = {0};
        float total = 0;
        int rank = 0;
        
        if (query_student_by_name_db(name, classroom, &id, daily_scores, final_scores, &total, &rank)) {
            printf("学生姓名：%s\n", name);
            printf("学号：%d\n", id);
            printf("班级：%s\n", classroom);
            printf("成绩详情：\n");
            for (int j = 0; j < 5; j++) {
                float course_score = daily_scores[j] * 0.4 + final_scores[j] * 0.6;
                printf("%s - 平时分：%.2f，期末分：%.2f，加权分：%.2f\n", 
                       COURSE_NAMES[j], daily_scores[j], final_scores[j], course_score);
            }
            printf("总成绩：%.2f\n", total);
            printf("在所有学生中排名：第%d名\n", rank);
        } else {
            printf("未找到该学生！\n");
        }
    } else {
        printf("无效的选择！\n");
    }
}

int query_student_by_id_db(int id, char* name, char* classroom, 
                          float* daily_scores, float* final_scores, float* total_score, int* rank) {
    char sql[2048];
    sqlite3_stmt* stmt;
    int rc;
    
    sprintf(sql, "SELECT s.name, s.classroom, "
                 "sc.course1_daily, sc.course1_final, "
                 "sc.course2_daily, sc.course2_final, "
                 "sc.course3_daily, sc.course3_final, "
                 "sc.course4_daily, sc.course4_final, "
                 "sc.course5_daily, sc.course5_final, "
                 "sc.total_score, "
                 "(SELECT COUNT(*) + 1 FROM scores sc2 WHERE sc2.total_score > sc.total_score) AS rank "
                 "FROM students s JOIN scores sc ON s.id = sc.student_id "
                 "WHERE s.id = %d", id);
    
    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL准备失败: %s\n", sqlite3_errmsg(db));
        return 0;
    }
    
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        strcpy(name, (const char*)sqlite3_column_text(stmt, 0));
        strcpy(classroom, (const char*)sqlite3_column_text(stmt, 1));
        
        for (int j = 0; j < 5; j++) {
            daily_scores[j] = sqlite3_column_double(stmt, j * 2 + 2);
            final_scores[j] = sqlite3_column_double(stmt, j * 2 + 3);
        }
        
        *total_score = sqlite3_column_double(stmt, 12);
        *rank = sqlite3_column_int(stmt, 13);
        
        sqlite3_finalize(stmt);
        return 1;
    }
    
    sqlite3_finalize(stmt);
    return 0;
}

int query_student_by_name_db(const char* name, char* classroom, int* id, 
                            float* daily_scores, float* final_scores, float* total_score, int* rank) {
    char sql[2048];
    sqlite3_stmt* stmt;
    int rc;
    char* escaped_name = escape_sql_string(name);
    
    if (!escaped_name) {
        printf("内存分配失败！\n");
        return 0;
    }
    
    sprintf(sql, "SELECT s.id, s.classroom, "
                 "sc.course1_daily, sc.course1_final, "
                 "sc.course2_daily, sc.course2_final, "
                 "sc.course3_daily, sc.course3_final, "
                 "sc.course4_daily, sc.course4_final, "
                 "sc.course5_daily, sc.course5_final, "
                 "sc.total_score, "
                 "(SELECT COUNT(*) + 1 FROM scores sc2 WHERE sc2.total_score > sc.total_score) AS rank "
                 "FROM students s JOIN scores sc ON s.id = sc.student_id "
                 "WHERE s.name = '%s'", escaped_name);
    
    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL准备失败: %s\n", sqlite3_errmsg(db));
        free(escaped_name);
        return 0;
    }
    
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        *id = sqlite3_column_int(stmt, 0);
        strcpy(classroom, (const char*)sqlite3_column_text(stmt, 1));
        
        for (int j = 0; j < 5; j++) {
            daily_scores[j] = sqlite3_column_double(stmt, j * 2 + 2);
            final_scores[j] = sqlite3_column_double(stmt, j * 2 + 3);
        }
        
        *total_score = sqlite3_column_double(stmt, 12);
        *rank = sqlite3_column_int(stmt, 13);
        
        sqlite3_finalize(stmt);
        free(escaped_name);
        return 1;
    }
    
    sqlite3_finalize(stmt);
    free(escaped_name);
    return 0;
}

void statistics_scores() {
    if (db == NULL && !init_database()) {
        printf("数据库初始化失败，无法统计成绩\n");
        return;
    }

    char classroom[MAX_NAME_LENGTH];
    printf("请输入要统计的班级：");
    scanf("%s", classroom);

    int count = 0;
    float total = 0;
    float max = -1;
    float min = 101;
    
    if (statistics_class_db(classroom, &count, &total, &max, &min)) {
        if (count == 0) {
            printf("该班级没有学生！\n");
        } else {
            printf("班级：%s\n", classroom);
            printf("学生人数：%d\n", count);
            printf("平均总分：%.2f\n", total / count);
            printf("最高总分：%.2f\n", max);
            printf("最低总分：%.2f\n", min);
        }
    } else {
        printf("统计失败！\n");
    }
}

int statistics_class_db(const char* classroom, int* count, float* total, float* max, float* min) {
    char sql[1024];
    sqlite3_stmt* stmt;
    char* escaped_classroom = escape_sql_string(classroom);
    
    if (!escaped_classroom) {
        printf("内存分配失败！\n");
        return 0;
    }
    
    sprintf(sql, "SELECT sc.total_score "
                 "FROM students s JOIN scores sc ON s.id = sc.student_id "
                 "WHERE s.classroom = '%s'", escaped_classroom);
    
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL准备失败: %s\n", sqlite3_errmsg(db));
        free(escaped_classroom);
        return 0;
    }
    
    *count = 0;
    *total = 0;
    *max = -1;
    *min = 101;
    
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        float score = sqlite3_column_double(stmt, 0);
        (*count)++;
        (*total) += score;
        if (score > *max) *max = score;
        if (score < *min) *min = score;
    }
    
    sqlite3_finalize(stmt);
    free(escaped_classroom);
    return 1;
}

float calculate_pass_score(float known_score, int course_idx, int score_type) {
    float required_score;
    
    if (score_type == 1) { // 已知平时分，计算需要的期末分
        required_score = (60 - known_score * 0.4) / 0.6;
    } else { // 已知期末分，计算需要的平时分
        required_score = (60 - known_score * 0.6) / 0.4;
    }
    
    return required_score;
}

void calculate_pass_score_interface() {
    if (db == NULL && !init_database()) {
        printf("数据库初始化失败，无法计算及格分\n");
        return;
    }

    printf("===== 及格分计算器 =====\n");
    printf("请选择要计算的科目：\n");
    for (int i = 0; i < 5; i++) {
        printf("%d. %s\n", i + 1, COURSE_NAMES[i]);
    }
    printf("请输入选择(1-5)：");
    
    int course_idx;
    scanf("%d", &course_idx);
    if (course_idx < 1 || course_idx > 5) {
        printf("无效的科目选择！\n");
        return;
    }
    course_idx--; // 转换为0-4的索引
    
    printf("请选择已知分数类型：\n1.平时分\n2.期末分\n请输入选择(1-2)：");
    int score_type;
    scanf("%d", &score_type);
    if (score_type != 1 && score_type != 2) {
        printf("无效的分数类型选择！\n");
        return;
    }
    
    float known_score;
    printf("请输入已知分数(0-100)：");
    scanf("%f", &known_score);
    if (known_score < 0 || known_score > 100) {
        printf("分数必须在0-100之间！\n");
        return;
    }
    
    float required_score = calculate_pass_score(known_score, course_idx, score_type);
    if (required_score < 0) {
        printf("即使已知分数为0，也能及格，无需额外努力！\n");
    } else if (required_score > 100) {
        printf("抱歉，即使已知分数为当前值，也无法通过该科目！\n");
    } else {
        printf("%s课程需要%s达到%.2f分才能及格\n", 
               COURSE_NAMES[course_idx], 
               (score_type == 1) ? "期末分" : "平时分", 
               required_score);
    }
}

void display_menu() {
    printf("\n===== 学生信息管理系统 =====\n");
    printf("1. 学生信息管理\n");
    printf("2. 成绩管理\n");
    printf("3. 及格分计算\n");
    printf("4. 退出系统\n");
    printf("===========================\n");
    printf("请选择操作：");
}

void display_student_menu() {
    printf("\n----- 学生信息管理 ----- \n");
    printf("1. 录入学生信息\n");
    printf("2. 更新学生信息\n");
    printf("3. 删除学生信息\n");
    printf("4. 返回主菜单\n");
    printf("------------------------ \n");
    printf("请选择操作：");
}

void display_score_menu() {
    printf("\n----- 成绩管理 ----- \n");
    printf("1. 成绩录入\n");
    printf("2. 成绩查询\n");
    printf("3. 成绩统计\n");
    printf("4. 返回主菜单\n");
    printf("-------------------- \n");
    printf("请选择操作：");
}

int main() {
    int main_choice, student_choice, score_choice;

    while (1) {
        display_menu();
        scanf("%d", &main_choice);

        switch (main_choice) {
            case 1:
                while (1) {
                    display_student_menu();
                    scanf("%d", &student_choice);
                    if (student_choice == 4) break;

                    switch (student_choice) {
                        case 1: add_student(); break;
                        case 2: update_student(); break;
                        case 3: delete_student(); break;
                        default: printf("无效的选择！\n");
                    }
                }
                break;

            case 2:
                while (1) {
                    display_score_menu();
                    scanf("%d", &score_choice);
                    if (score_choice == 4) break;

                    switch (score_choice) {
                        case 1: input_scores(); break;
                        case 2: query_scores(); break;
                        case 3: statistics_scores(); break;
                        default: printf("无效的选择！\n");
                    }
                }
                break;

            case 3:
                calculate_pass_score_interface();
                break;

            case 4:
                printf("感谢使用学生信息管理系统，再见！\n");
                close_database();
                return 0;

            default:
                printf("无效的选择！\n");
        }
    }
}    
